/**
 * AccessBD
 * 
 * 09/10/2013
 * 
 * Copyright GPL
 */
package info.victor.utils;

import info.victor.content.Tale;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


/**
 * This class accesses and manipulates the DataBase
 */
public class AccessDB {

	private static final String USER = "root";
	private static final String PASSWORD = "";
	private static final String URL_CONNECTION = 
			"jdbc:mysql://localhost:3306/onceupon";

	private Connection connection = null;
	private Statement stat = null;
	private ResultSet resultSet = null;

	/**
	 * this method connects to the DB
	 */
	private void connectDB(){
		try {

			Class.forName("com.mysql.jdbc.Driver");
			connection = 
					DriverManager.getConnection(
							URL_CONNECTION, USER, PASSWORD);
			stat = connection.createStatement();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * this method disconnects to the DB 
	 */
	private void disconnectDB(){
		try {

			if (resultSet != null){
				resultSet.close();
				resultSet = null;
			}
			
			if (connection != null){
				connection.close();
				connection = null;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	/**
	 * this function returns all the tales
	 * @return
	 */
	public ArrayList<Tale> getAllTales(){
		ArrayList<Tale> listOfTales = new ArrayList<Tale>();
		listOfTales.clear();

		connectDB();

		try {
			resultSet = stat.executeQuery("SELECT * FROM tale");

			while(resultSet.next()){
				listOfTales.add(new Tale(resultSet.getInt("id"),
						resultSet.getInt("iduser"),
						resultSet.getDate("creationdate"),
						resultSet.getString("title"),
						resultSet.getString("tale")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		disconnectDB();

		return listOfTales;
	}

	/**
	 * this function returns all the tales
	 * @return
	 */
	public Tale getTale(String id){
		Tale tale = null;

		connectDB();

		try {
			resultSet = stat.executeQuery
					("SELECT * FROM tale WHERE id = " + id);

			while(resultSet.next()){
				tale = new Tale(resultSet.getInt("id"),
						resultSet.getInt("iduser"),
						resultSet.getDate("creationdate"),
						resultSet.getString("title"),
						resultSet.getString("tale"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		disconnectDB();

		return tale;
	}

	/**
	 * this inserts a tale into DB
	 * @param title
	 * @param tale
	 * @return
	 */
	public boolean insertTaleIntoDB(String title, String tale){
		int insertedRows = 0;

		connectDB();

		try {
			
			String insert = 
					"INSERT INTO tale (iduser, title, tale) VALUES (?, ?, ?)";
			PreparedStatement preparedStatement = 
					connection.prepareStatement(insert);
			
			preparedStatement.setInt(1, 1);
			preparedStatement.setString(2, title);
			preparedStatement.setString(3, tale);
			
			insertedRows = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		disconnectDB();
		return insertedRows > 0;
	}

	/**
	 * this function deletes a row from the table tale
	 * @param id
	 * @return
	 */
	public boolean deleteTale(String id){
		int deleteRows = 0;

		connectDB();

		try {
			String deleteRow ="DELETE FROM tale WHERE id = ?";
			
			PreparedStatement preparedStatement = 
					connection.prepareStatement(deleteRow);
			preparedStatement.setString(1, id);
			deleteRows = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		disconnectDB();
		return deleteRows > 0;
	}
	
	/**
	 * this method updates a tale
	 * @param id
	 * @param title
	 * @param tale
	 * @return
	 */
	public boolean updateTale(String id, String title, String tale){
		int updateTale = 0;

		connectDB();

		try {
			
			String update = "UPDATE tale SET title = ?, tale = ? WHERE id = ?";
			
			PreparedStatement preparedStatement = 
					connection.prepareStatement(update);
			
			preparedStatement.setString(1, title);
			preparedStatement.setString(2, tale);
			preparedStatement.setString(3, id);
			
			updateTale = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		disconnectDB();
		return updateTale > 0;
	}
}
